package com.junzhiit.nengji.shop.dao.impl;

import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.shop.bo.NoticeBO;
import com.junzhiit.nengji.shop.dao.NoticeDAO;
import com.junzhiit.nengji.shop.model.Notice;
import com.junzhiit.nengji.shop.model.NoticeRead;
import com.junzhiit.nengji.shop.model.enums.Is;
import com.junzhiit.nengji.shop.model.enums.NoticeStatus;
import com.junzhiit.nengji.shop.model.enums.NoticeType;
import com.junzhiit.nengji.shop.model.enums.Status;

@Repository
public class NoticeDAOImpl extends AbstractBaseDAO<Notice> implements NoticeDAO {

	@Override
	public List<NoticeBO> findAll(int pageNumber, int maxPageRow, String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select n.*,s.name 'shop_name',su.name 'issuer_name',su.mobile_phone from `notice` n");
		sql.append(" left join `shop` s on n.shop_id=s.id");
		sql.append(" left join system_user su on su.id=n.issuer");
		sql.append(" where n.is_delete=?");

		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());

		if (StringUtils.isNotBlank(shopId)) {
			sql.append(" and n.shop_id=?");
			params.setString(shopId);
		} else {
			sql.append(" and n.type=?");
			params.setInt(NoticeType.BASE.toIntValue());
		}
		sql.append(" order by n.sort desc,n.created_time desc");

		return queryForPagingList(sql.toString(), NoticeBO.class, params, pageNumber, maxPageRow);
	}

	@Override
	public NoticeBO findById(String id) {
		StringBuffer sql = new StringBuffer();
		sql.append("select n.*,s.name 'shop_name',su.name 'issuer_name',su.mobile_phone from `notice` n");
		sql.append(" left join `shop` s on n.shop_id=s.id");
		sql.append(" left join system_user su on su.id=n.issuer");
		sql.append(" where n.id=?");

		StatementParameter params = new StatementParameter();
		params.setString(id);

		return query(sql.toString(), NoticeBO.class, params);
	}

	@Override
	public Integer getTotalCount(String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(id) from `notice` where is_delete=?");

		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());

		if (StringUtils.isNotBlank(shopId)) {
			sql.append(" and shop_id=?");
			params.setString(shopId);
		} else {
			sql.append(" and type=?");
			params.setInt(NoticeType.BASE.toIntValue());
		}

		return queryForInt(sql.toString(), params);
	}

	@Override
	public Integer getMaxSort(String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select max(sort) from `notice` where is_delete=?");

		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());

		if (StringUtils.isNotBlank(shopId)) {
			sql.append(" and shop_id=?");
			params.setString(shopId);
		} else {
			sql.append(" and type=?");
			params.setInt(NoticeType.BASE.toIntValue());
		}

		String str = queryForString(sql.toString(), params);
		if (StringUtils.isNotBlank(str)) {
			return Integer.valueOf(str);
		}
		return 0;
	}

	@Override
	public List<Notice> getFailureNotice(Date date) {
		String sql = "select * from notice where is_delete=? and status<>? and end_time < ?";
		StatementParameter params = new StatementParameter();

		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(NoticeStatus.FAILURE.toIntStatus());
		params.setDate(date);

		return queryForList(sql, Notice.class, params);
	}

	@Override
	public List<NoticeBO> findAllNoticeNews(int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append("select n.*,s.name 'shop_name',su.name 'issuer_name',su.mobile_phone from `notice` n");
		sql.append(" left join `shop` s on n.shop_id=s.id");
		sql.append(" left join system_user su on su.id=n.issuer");
		sql.append(" where n.is_delete=? and n.status=? and n.is_shop=? and n.type<>?");
		sql.append(" order by n.sort desc,n.created_time desc");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(Status.ACTIVE.toIntStatus());
		param.setInt(Is.YES.toIntValue());
		param.setInt(NoticeType.SHOP.toIntValue());

		return queryForPagingList(sql.toString(), NoticeBO.class, param, pageNumber, maxPageRow);
	}

	@Override
	public Integer getNoticeNewsCount() {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from `notice` n");
		sql.append(" where n.is_delete=? and n.status=? and n.is_shop=? and n.type<>?");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(Status.ACTIVE.toIntStatus());
		param.setInt(Is.YES.toIntValue());
		param.setInt(NoticeType.SHOP.toIntValue());

		return queryForInt(sql.toString(), param);
	}

	@Override
	public List<NoticeBO> findAllUnread(int pageNumber, int maxPageRow, String shopId, NoticeRead noticeRead) {
		StringBuffer sql = new StringBuffer();
		sql.append("select n.*,s.name 'shop_name',su.name 'issuer_name',su.mobile_phone from `notice` n");
		sql.append(" left join `shop` s on n.shop_id=s.id");
		sql.append(" left join system_user su on su.id=n.issuer");
		sql.append(" where 1=1");
		StatementParameter params = new StatementParameter();
		// 排除
		if (StringUtils.isNotBlank(noticeRead.getFinishReading())) {
			sql.append(" and find_in_set(n.created_time,?)<=0");
			params.setString(noticeRead.getFinishReading());
		}
		// 比这个时间大
		if (noticeRead.getPastRecord() != null) {
			sql.append(" and n.created_time > ?");
			params.setDate(noticeRead.getPastRecord());
		}
		// 比这个时间小或者比这个时间大
		if (noticeRead.getCurrentRecord() != null) {
			sql.append(" and n.created_time <> ? ");
			params.setDate(noticeRead.getCurrentRecord());
		}
		
		sql.append(" and n.is_delete=? and n.status=? and n.is_shop=? and n.type<>? ");
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(Status.ACTIVE.toIntStatus());
		params.setInt(Is.YES.toIntValue());
		params.setInt(NoticeType.SHOP.toIntValue());
		
		sql.append(" order by n.sort desc,n.created_time desc");
		return queryForPagingList(sql.toString(), NoticeBO.class, params, pageNumber, maxPageRow);
	}

	@Override
	public Integer getUnreadTotalCount(String shopId, NoticeRead noticeRead) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(id) from `notice` n where 1=1");

		StatementParameter params = new StatementParameter();
		

		// 排除
		if (StringUtils.isNotBlank(noticeRead.getFinishReading())) {
			sql.append(" and find_in_set(n.created_time,?)<=0");
			params.setString(noticeRead.getFinishReading());
		}
		// 比这个时间大
		if (noticeRead.getPastRecord() != null) {
			sql.append(" and n.created_time > ?");
			params.setDate(noticeRead.getPastRecord());
		}
		// 比这个时间小或者比这个时间大
		if (noticeRead.getCurrentRecord() != null) {
			sql.append(" and n.created_time <> ? ");
			params.setDate(noticeRead.getCurrentRecord());
		}

		sql.append(" and n.is_delete=? and n.status=? and n.is_shop=? and n.type<>? ");
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(Status.ACTIVE.toIntStatus());
		params.setInt(Is.YES.toIntValue());
		params.setInt(NoticeType.SHOP.toIntValue());
		return queryForInt(sql.toString(), params);
	}

	@Override
	public Integer getUnreadTotalCountSubrange(String shopId, NoticeRead noticeRead) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(id) from `notice` n where 1=1");

		StatementParameter params = new StatementParameter();

		// 排除
		if (StringUtils.isNotBlank(noticeRead.getFinishReading())) {
			sql.append(" and find_in_set(n.created_time,?)<=0");
			params.setString(noticeRead.getFinishReading());
		}
		// 比这个时间大
		if (noticeRead.getPastRecord() != null) {
			sql.append(" and n.created_time > ?");
			params.setDate(noticeRead.getPastRecord());
		}
		// 比这个时间小或者比这个时间大
		if (noticeRead.getCurrentRecord() != null) {
			sql.append(" and n.created_time < ?");
			params.setDate(noticeRead.getCurrentRecord());
		}
		
		sql.append(" and n.is_delete=? and n.status=? and n.is_shop=? and n.type<>? ");
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(Status.ACTIVE.toIntStatus());
		params.setInt(Is.YES.toIntValue());
		params.setInt(NoticeType.SHOP.toIntValue());
		
		return queryForInt(sql.toString(), params);
	}
}
